package cn.dglydrpy.study.j2ee.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Scanner;

import org.junit.Test;

import cn.dglydrpy.study.j2ee.jdbc.utils.DBUtil;

/**
 * jdbc: Java数据库连接 Sun(Java) 设计了JDBC API底层封装了Socket, 简化数据库的访问.JDBC为数据库提供了统一访问接口.
 */
public class JDBCDemo {

	/*
	 * 不使用工具类的最原始版本
	 */
	@Test
	public void originalJDBC() {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try {
			// 1.注册驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 2.获取连接对象
			// JDBC 连接常用参数, 写在连接url上:(用?拼接)
			// characterEncoding 字符编码, 可以设置为utf8
			// useUnicode 是否使用unicode字符编码, 设置为true
			// 关闭ssl加密, useSSL 设置为false
			String usr = "root";
			String pwd = "147258";
			String url = "jdbc:mysql://localhost:3306/j2ee?characterEncoding=utf8&useUnicode=true&useSSL=false";
			conn = DriverManager.getConnection(url, usr, pwd);
			// 3.创建sql执行对象
			stat = conn.createStatement();
			// 4.准备sql语句
			String sql_a = "desc t_jdbc"; // DDL
			String sql_b = "alter table t_jdbc change sex gender char(1)";// DDL
			String sql_c = "select * from t_jdbc";// DQL
			String sql_d = "update t_jdbc set age=20 where id=3";// DML
			// 5.执行sql语句的3种方式:
			// DDL 一般使用 execute
			// DML (增删改)一般使用 executeUpdate
			// DQL (查)一般使用 executeQuery

			// execute可执行任意sql 返回boolean值表示有无结果集
			boolean b1 = stat.execute(sql_a);
			if (b1) {// 有结果集
				ResultSet rss = stat.getResultSet();
				while (rss.next()) {
					System.out.println(rss.getString(1) + "," + rss.getString(2));
				}
			} else {
				System.out.println("无结果集");
			}

			boolean b2 = stat.execute(sql_b);
			if (b2) {
				System.out.println("生效行数:" + stat.getUpdateCount());// 获得生效行数
			} else {
				System.out.println("无结果集");
			}

			// executeQuery一般执行查询sql 返回值对象装着结果数据
			rs = stat.executeQuery(sql_c);
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				int age = rs.getInt(3);
				String sex = rs.getString(4);
				System.out.println(id + "," + name + "," + age + "," + sex);
			}
			// executeUpdate一般执行增删改sql 返回生效行数
			int count = stat.executeUpdate(sql_d);
			System.out.println("生效行数:" + count);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// 关闭流连接
				if (rs != null) { // 可以不关闭rs, conn关闭时候自动关闭rs
					rs.close();
				}
				if (stat != null) {
					stat.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/*
	 * 使用封装工具类的版本
	 */
	@Test
	public void insertWithUtil() {
		Connection conn = null;
		Statement stat = null;
		try {
			conn = DBUtil.getConn();
			stat = conn.createStatement();
			String sql = "insert into t_jdbc values(4,'小乔','0',20)";
			int row = stat.executeUpdate(sql);
			System.out.println("生效行数:" + row);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stat, null);
		}

	}

	@Test
	public void deleteWithUtil() {
		Connection conn = null;
		Statement stat = null;
		try {
			conn = DBUtil.getConn();
			stat = conn.createStatement();
			String sql = "delete from t_jdbc where id=4";
			int row = stat.executeUpdate(sql);
			System.out.println("生效行数:" + row);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stat, null);
		}
	}

	@Test
	public void updateWithUtil() {
		Connection conn = null;
		Statement stat = null;
		try {
			conn = DBUtil.getConn();
			stat = conn.createStatement();
			String sql = "update t_jdbc set name='曹植' where id=2";
			int row = stat.executeUpdate(sql);
			System.out.println(row);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stat, null);
		}

	}

	@Test
	public void findAllWithUtil() {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConn();
			stat = conn.createStatement();
			rs = stat.executeQuery("select * from t_jdbc");
			while (rs.next()) { // 底层通过游标进行遍历
				int id = rs.getInt(1); // 位置,从1开始
				String name = rs.getString("name");
				String gender = rs.getString("gender");
				int age = rs.getInt(4);
				System.out.println("id:" + id + " name:" + name + "  gender:" + gender + " age:" + age);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stat, rs);
		}
	}

	/**
	 * PreparedStatement 预编译sql 1. 将带有参数的SQL发送到数据库, 编译成执行计划. 2. 设置执行计划的参数值 3. 发送参数,
	 * 执行数据库的执行计划
	 * 
	 * 好处:
	 * 1. 复用执行计划: SQL固定不变, 数据库端可以重用相同的执行计划, 提高了执行效率     每当 SQL改变时, 数据库会花费高昂的计算成本创建执行计划
	 * 2. 可以避免SQL注入!!   SQL注入是常见的安全漏洞现象, 原因是用户提交的数据中包含SQL语句成分, 服务器端拼接SQL时候,
	 * SQL成分改变了SQL语句执行逻辑, 造成SQL执行了注入攻击逻辑.
	 * 
	 * > 重要: SQL拼接时候, 不要拼接用户提交的字符串!!!!
	 */
	@Test
	public void insertByPreparedStatement() {
		/**
		 * 测试有参数的插入语句
		 */
		System.out.println(add(5, "Jerry", "0", 15));
		System.out.println(add(6, "熊大", "1", 3));
		System.out.println(add(7, "熊二", "1", 2));
	}

	public static int add(int id, String name, String gender, int age) {

		String sql = "insert into t_jdbc (id, name,gender,age) " + "values (?,?,?,?)";
		Connection conn = null;
		try {
			conn = DBUtil.getConn();
			// 发送SQL到服务器, 编译执行计划
			PreparedStatement ps = conn.prepareStatement(sql);
			// 替换执行计划参数
			ps.setInt(1, id); // 绑定第一个参数
			ps.setString(2, name); // 绑定第二个参数
			ps.setString(3, gender);
			ps.setInt(4, age);
			// 执行编译好的执行计划
			int n = ps.executeUpdate(); // 不要传递SQL参数!!!
			return n;
		} catch (Exception e) {
			e.printStackTrace();
			return 0; // 返回0表示插入失败
		} finally {
			DBUtil.close(conn, null, null);
		}
	}

	@Test
	public void updateByPreparedStatement() {
		/**
		 * 更新数据
		 */
		System.out.println(update(3, "小兰"));
		System.out.println(update(1, "小明"));
	}

	public static int update(int id, String name) {
		String sql = "update t_jdbc set name=? " + "where id=? ";
		Connection conn = null;
		try {
			conn = DBUtil.getConn();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(2, id); // 绑定第一个参数
			ps.setString(1, name); // 绑定第二个参数
			int n = ps.executeUpdate(); // 不要传递SQL参数!!!
			return n;
		} catch (Exception e) {
			e.printStackTrace();
			return 0; // 返回0表示插入失败
		} finally {
			DBUtil.close(conn, null, null);
		}

	}

	@Test
	public void selectByPreparedStatement() {
		/**
		 * 执行带参数是SQL查询
		 */
		findById(3);
		findById(5);
	}

	public static void findById(int id) {
		String sql = "select * from t_jdbc " + "where id=?";
		Connection conn = null;
		try {
			conn = DBUtil.getConn();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int uid = rs.getInt("id");
				String name = rs.getString("name");
				String gender = rs.getString("gender");
				int age = rs.getInt("age");
				System.out.println(uid + "," + name + "," + gender + "," + age);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, null, null);
		}
	}

	/**
	 * 批量操作
	 * 大量SQL操作时候, 使用批量执行可以提升执行效率
	 */
	@Test
	public void batchInsertByStatement() {
		// 往数据库中插入 刘关张三个人的信息
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConn();
			stat = conn.createStatement();

			String[] sqlArray = { // 可以是不同类型的sql
					"insert into t_jdbc values(null,'刘备','1',40)", "insert into t_jdbc values(null,'关羽','1',30)",
					"insert into t_jdbc values(null,'张飞','1',20)" };

			// 把要执行的sql 添加到一个批量处理里面
			stat.addBatch(sqlArray[0]);
			stat.addBatch(sqlArray[1]);
			stat.addBatch(sqlArray[2]);
			// executeBatch将一批SQL一起发送到数据库执行 返回值代表每一行sql语句执行生效的行数
			int[] arr = stat.executeBatch();
			for (int i : arr) {
				System.out.println(i);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stat, rs);
		}
	}

	@Test
	public void batchInsertByPreparedStatement() {
		/**
		 * 将批量参数传递到数据库执行
		 */
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConn();
			String sql = "insert into t_jdbc values" + "(null,?,?,?)";
			stat = conn.prepareStatement(sql);
			stat.setString(1, "八戒");
			stat.setInt(3, 200);
			stat.setString(2, "0");
			stat.addBatch();

			stat.setString(1, "悟空");
			stat.setInt(3, 500);
			stat.setString(2, "1");
			stat.addBatch();
			int[] rows = stat.executeBatch();
			System.out.println(Arrays.toString(rows)); 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stat, rs);
		}

	}

	@Test
	public void batchInsert() {
		
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConn();
			String sql = "insert into t_jdbc values" + "(null,?,?,?)";
			stat = conn.prepareStatement(sql);
			for (int i = 0; i < 1000; i++) {
				stat.setString(1, "name" + i);
				stat.setInt(3, i);
				stat.setString(2, i % 2 == 0 ? "1" : "0");
				stat.addBatch();
				// 每隔20条执行一次
				if (i % 20 == 0) {
					stat.executeBatch();
					// 清空之前执行过的sql
					stat.clearBatch();
				}
			}
			stat.executeBatch();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stat, rs);
		}
	}
	
	/**
	 * 事务操作
	 */
	@Test
	public void transactionDeal() {
		/**
		 * 事务演示
		 * create table acc(
			id int,
			name varchar(100),
			balance double
		);

		insert into acc (id, name, balance) 	
		values (1, '小明', 1000);
		insert into acc (id, name, balance) 	
		values (2, '小红', 200);
		 */
		trans(1, 2, 2000); 
		trans(3, 2, 500); 
		trans(1, 3, 500);
		trans(1, 2, 500);
	}

	/**
	 * 从 from 账号 向 to 账号转钱, 钱数是money
	 */
	private void trans(int from, int to, int money) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConn();
			//关闭自动提交  开启事务
			conn.setAutoCommit(false);
			String sql1 = "update acc set "
					+ "balance=balance? where id=?";
			String sql2 = "SELECT balance from acc where id=?";
		    ps = conn.prepareStatement(sql1);
			ps.setDouble(1, -money); //-500
			ps.setInt(2, from);
			int n = ps.executeUpdate();
			if(n!=1) throw new Exception("汇出失败"); 
			ps.setDouble(1, money); //500
			ps.setInt(2, to); 
			n = ps.executeUpdate();
			if(n!=1) throw new Exception("汇入失败"); 
			ps.close();//ps对象用完了, 关闭释放
			
			//检查汇出人的余额
			ps = conn.prepareStatement(sql2); //重用ps变量
			ps.setInt(1, from); 
			rs = ps.executeQuery();
			while(rs.next()) {
				double balance = rs.getDouble("balance");
				if(balance<0) throw new Exception("透支");
			}
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
	}
	
	/**
	 * 读取自动生成的ID
	 */
	@Test
	public void generatePrimaryKey() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConn();
			String sql = "insert into dept values"
					+ "(null,?)";
			
			//如果使用statement 执行sql并设置需要得到自增的值
			//stat.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
			
			ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			ps.setString(1, "10"); 
			ps.executeUpdate();//ps中已经包含了生成的keys
			//获取自增主键值
			rs = ps.getGeneratedKeys();
			ps.close();
			int deptid = 0;
			while(rs.next()){
				deptid = rs.getInt(1); //只能使用序号读取,1是第一个数据序号
			}
			//准备插入员工的sql
			String sql2 = "insert into emp values"
					+ "(null,?,?)";
			
			ps = conn.prepareStatement(sql2); //共用ps
			ps.setString(1, "观音");
			ps.setLong(2,deptid);
			ps.executeUpdate(sql2);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
	}
	
	/**
	 * 获取元数据
	 */
	@Test
	public static void getMetaData() {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConn();
			stat = conn.createStatement();
			//得到数据库元数据
			DatabaseMetaData dbmd = conn.getMetaData();
			System.out.println("驱动版本:"
					+dbmd.getDriverVersion());
			System.out.println("用户名:"
					+dbmd.getUserName());
			System.out.println("连接地址:"
					+dbmd.getURL());
			System.out.println("数据库厂商:"
					+dbmd.getDatabaseProductName());
			System.out.println("数据库版本:"
					+dbmd.getDatabaseProductVersion());
			rs = stat.executeQuery(
					"select * from student");
			//从结果集中得到表相关的元数据
			ResultSetMetaData rsmd = rs.getMetaData();
			//得到表字段的数量
			int columCount = rsmd.getColumnCount();
			for (int i = 0; i < columCount; i++) {
				String name = rsmd.getColumnName(i+1);
				System.out.print(name+"\t");
			}
			System.out.println();
			while(rs.next()) {
				System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getInt(3)+"\t"+rs.getInt(4)+"\t"+rs.getInt(5));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stat, rs);
		}
	}
	
	/**
	 * limit分页
	 */
	@Test
	public void limit() {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入查询的页数");
		int page = sc.nextInt();
		System.out.println("请输入每页的条数");
		int size = sc.nextInt();
		
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConn();
			String sql = "select * from student limit ?,?";
			stat = conn.prepareStatement(sql);
			//设置跳过的条数
			stat.setInt(1, (page-1)*size);
			//设置每页的数量
			stat.setInt(2, size);
			//执行查询
			rs = stat.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				System.out.println("id:"+id+" name:"+name);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stat, rs);
		}
	}
	
	/**
	 * 调用存储过程
	 */
	public void callableStatement() {
		Connection conn = null;
		CallableStatement stat = null;
		ResultSet rs = null;
		
		try {
			conn = DBUtil.getConn();
			String sql = "call sp_search(?)";
			stat = conn.prepareCall(sql);
			stat.setString(1, null);
			rs = stat.executeQuery();
			while(rs.next()) {
				String id = rs.getString("id");
				String name = rs.getString("name");
				String age = rs.getString(3);
				String sex = rs.getString("sex");
				String deptid = rs.getString(5);
				System.out.println(id+","+name+","+age+","+sex+","+deptid);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stat, rs);
		}
	}
}
